-------------------------------------------------------------------------------
--
-------------------------------------------------------------------------------
require("luasql.mysql")

dofile("common.lua")

-------------------------------------------------------------------------------
-- Initialize the database
-------------------------------------------------------------------------------
function db_Initialize ()
	-- Drop the existe tables
	db_Execute(string.format([[DROP TABLE IF EXISTS master_peer]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS session]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS token]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS verificationQueue]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS vote]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS voteQueue]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS voteResult]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS voteResultUrlUrl]]))
	db_Execute(string.format([[DROP TABLE IF EXISTS voteResultQryUrl]]))

	-- Create the tables
	-- Table master_peer
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS master_peer(ID int(11) NOT NULL AUTO_INCREMENT, 
		userID int(11) NOT NULL, 
		email varchar(40) NOT NULL, 
		password varchar(40) NOT NULL, 
		status varchar(40) NOT NULL, 
		IPAP varchar(40), 
		sessionID int(11), 
		timeStart varchar(20), 
		primary key (ID))
	]]))
	
	-- Table token
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS token(ID int(11) NOT NULL AUTO_INCREMENT, 
		tokenID varchar(40) NOT NULL, 
		userID int(11) NOT NULL, 
		c1 varchar(40) NOT NULL, 
		c2 varchar(40) NOT NULL, 
		status varchar(40) NOT NULL, 
		timeCreate varchar(20) NOT NULL, 
		primary key (ID))
	]]))
	
	-- Table vote
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS vote(ID int(11) NOT NULL AUTO_INCREMENT, 
		voteSessionID varchar(40) NOT NULL,
		url varchar(1024) NOT NULL, 
		status varchar(40) NOT NULL,
		primary key (ID))
	]]))

	-- Table session
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS session(ID int(11) NOT NULL AUTO_INCREMENT, 
		sessionID int(11) NOT NULL, 
		userID int(11) NOT NULL, 
		IPMP varchar(40) NOT NULL, 
		timeStart varchar(20), 
		primary key(ID))
	]]))
	
	-- Table voteQueue
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS voteQueue(ID int(11) NOT NULL AUTO_INCREMENT,
	    voteSessionID varchar(40) NOT NULL,
	    code varchar(40) NOT NULL,
	    messageCrypt varchar(100),
	    primary key (ID));
	]]))
	
	-- Table verificationQueue
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS verificationQueue(ID int(11) NOT NULL AUTO_INCREMENT,
	    voteSessionID varchar(40) NOT NULL,
	    c1 varchar(40) NULL,
	    c2 varchar(40) NULL,
	    IPMP varchar(40) NULL,
	    primary key (ID));
	]]))	
	
	-- Table voteResult
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS voteResult(ID int(11) NOT NULL AUTO_INCREMENT,
    	url varchar(256) NOT NULL,
    	numberVote int(11) NOT NULL,
    	longitude varchar(20) NULL,
    	latitude varchar(20) NULL,
    	primary key (ID));
	]]))

	-- Table voteResultUrlUrl
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS voteResultUrlUrl(ID int(11) NOT NULL AUTO_INCREMENT,
    	url1 varchar(256) NOT NULL,
    	url2 varchar(256) NOT NULL,
    	score int(11) NOT NULL,
    	primary key (ID));
	]]))

	-- Table voteResultQryUrl
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS voteResultQryUrl(ID int(11) NOT NULL AUTO_INCREMENT,
    	query varchar(256) NOT NULL,
    	url varchar(256) NOT NULL,
    	score int(11) NOT NULL,
    	primary key (ID));
	]]))
end

------------------------------------------------------------------------------
-- Reset the configuration's table
-------------------------------------------------------------------------------
function config_Reset()
	-- Drop the existe tables
	db_Execute(string.format([[DROP TABLE IF EXISTS configuration]]))
	
	-- Create table configuration
	db_Execute(string.format([[
		CREATE TABLE IF NOT EXISTS configuration(ID int(11) NOT NULL AUTO_INCREMENT,
		WEB_PORT varchar(40) NOT NULL,
	    WEB_SESSION_DIR varchar(100) NOT NULL,
	    WEB_SESSION_TIME_OUT varchar(40) NOT NULL,
	    MYSQL_DATABASE varchar(40) NOT NULL,
	    MYSQL_USER varchar(40) NOT NULL,
	    MYSQL_PASSWORD varchar(40) NOT NULL,
	    MYSQL_HOSTID varchar(40) NOT NULL,
	    MYSQL_PORT varchar(40) NOT NULL,
	    SMTP_SERVER_NAME varchar(40) NOT NULL,
	    SMTP_SERVER_PORT varchar(40) NOT NULL,
	    SMTP_USER varchar(40) NOT NULL,
	    SMTP_PASSWORD varchar(40) NOT NULL,
	    RECAPTCHA_PUBLIC_KEY varchar(50) NOT NULL,
	    RECAPTCHA_PRIVATE_KEY varchar(50) NOT NULL,
	    TOKEN_TIME_OUT varchar(40) NOT NULL,
	    TOKEN_GET_PERIOD varchar(40) NOT NULL,
	    TOKEN_GET_NUMBER varchar(40) NOT NULL,
	    GOOGLE_MAP_KEY varchar(100) NOT NULL,
	    SPLAYD_PORT varchar(40) NOT NULL, 
	    VOTE_PEER_IP varchar(40) NOT NULL,
	    primary key (ID));
	]]))
	
	-- Insert the data in the config table
	db_Execute(string.format([[
		INSERT INTO configuration (
	    WEB_PORT,
	    WEB_SESSION_DIR,
	    WEB_SESSION_TIME_OUT,
	    MYSQL_DATABASE,
	    MYSQL_USER,
	    MYSQL_PASSWORD,
	    MYSQL_HOSTID,
	    MYSQL_PORT,
	    SMTP_SERVER_NAME,
	    SMTP_SERVER_PORT,
	    SMTP_USER,
	    SMTP_PASSWORD,
	    RECAPTCHA_PUBLIC_KEY,
	    RECAPTCHA_PRIVATE_KEY,
	    TOKEN_TIME_OUT,
	    TOKEN_GET_PERIOD,
	    TOKEN_GET_NUMBER,
	    GOOGLE_MAP_KEY,
	    SPLAYD_PORT,
	    VOTE_PEER_IP
	    )
	    VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')]],
	        "9999", -- WEB_PORT
	        "/tmp/luasession", -- WEB_SESSION_DIR
	        "1200", -- WEB_SESSION_TIME_OUT
	        "distrinet", -- MYSQL_DATABASE
	        "distrinet", -- MYSQL_USER
	        "distrinet", -- MYSQL_PASSWORD
	        "localhost", -- MYSQL_HOSTID
	        "3306", -- MYSQL_PORT
	        "mail.epfl.ch", -- SMTP_SERVER_NAME
	        "25", -- SMTP_SERVER_PORT
	        "huuduc.tran@epfl.ch", -- SMTP_USER
	        "", -- SMTP_PASSWORD
	        "6Ld5QwcAAAAAAAUWGG53Jkk3WZ3Ji8PjV8crXZmd", -- RECAPTCHA_PUBLIC_KEY
	        "6Ld5QwcAAAAAADIPTofkjda8wa67A961l9dAGzz8", -- RECAPTCHA_PRIVATE_KEY
	        "600", -- TOKEN_TIME_OUT (s)
	        "60", -- TOKEN_GET_PERIOD(s)
	        "2", -- TOKEN_GET_NUMBER
	        "ABQIAAAAtQgjkzML9HvC0NJ3Y6lEehRr65xaCOqEfdnEVKzW7YqQaV_5KBQapvyLCtFCBwQKq6SBwf6EQa4lnA", -- GOOGLE_MAP_KEY for IP : 128.178.155.92
	        "60000", -- SPLAYD_PORT
	        "128.178.155.92" -- VOTE_PEER_IP);
	))
end

-------------------------------------------------------------------------------
-- Set parametre function
-------------------------------------------------------------------------------
function setParameter(parameterName, parameterValue)
	local name = tostring(parameterName)
	local value = tostring(parameterValue)
	local executionStr = "UPDATE configuration SET "..name.." = "
	executionStr = executionStr..string.format([['%s']], value)
	local execution = db_Execute(executionStr)
end

